package com.example.springbootbabyu.mapper;

import com.example.springbootbabyu.entity.*;
import com.example.springbootbabyu.entity.vo.SkusVo;
import org.apache.ibatis.annotations.*;

import java.util.List;

/**
 * 功能
 * 作者：梦天
 * 日期：2025/4/21 21:52
 */
@Mapper
public interface MemberMapper {
    @Select("SELECT " +
            "c.id AS id, " +
            "c.sku_id AS skuId, " +
            "g.name AS name, " +
            "c.attrs_text AS attrsText, " +
            "s.specs AS specs, " +
            "COALESCE(s.picture, g.picture) AS picture, " +
            "s.old_price AS price, " +
            "s.price AS nowPrice, " +
            "s.old_price AS nowOriginalPrice, " +
            "c.selected AS selected, " +
            "s.stock AS stock, " +
            "c.count AS count, " +
            "(s.stock >= c.count) AS isEffective, " +
            "s.discount AS discount, " +
            "false AS isCollect, " +
            "0 AS postFee " +
            "FROM cart c " +
            "INNER JOIN sku s ON c.sku_id = s.id " +
            "INNER JOIN goods g ON s.goods_id = g.id " +
            "WHERE c.user_id = #{userId}")
    List<CartVo> getCard(String userId);

    @Select("SELECT * FROM pre_order WHERE user_id = #{userId} AND status = 0")
    PreOrderVo getPreOrder(String userId);

    @Select("SELECT * FROM order_info WHERE order_id = #{orderId}")
    String mergeOrder(String orderId);
    @Select("INSERT INTO cart (user_id, sku_id, count, selected) VALUES (#{userId}, #{skuId}, #{count} , #{selected})")
    String mergeCart(String userId, String skuId, Integer count, Boolean selected);

    @Select("SELECT * FROM cart WHERE user_id = #{userId}")
    CartMergeDTO getMergeCart(String userId);

    @Select("SELECT * FROM user_address WHERE user_id = #{userId}")
    List<UserAddressVo> getUserAddress(String userId);
    @Select("SELECT c.sku_id, g.name, s.picture, " +
            "c.attrs_text AS attrsText, " +
            "s.price, " +
            "c.count, " +
            "s.price * c.count AS totalPrice, " +
            "s.price * c.count * (1 - COALESCE(s.discount, 0) / 100) AS totalPayPrice " +
            "FROM cart c " +
            "JOIN sku s ON c.sku_id = s.id " +
            "JOIN goods g ON s.goods_id = g.id " +
            "WHERE c.user_id = #{userId} AND c.selected = true")
    List<OrderGoodsVo> getOrderGoods(String orderId);

    @Select("INSERT INTO cart (user_id, sku_id, count) VALUES (#{userId}, #{skuId}, #{count})")
    CartItemDTO addCart(String userId, String skuId, Integer count);

    @Select("DELETE FROM cart WHERE user_id = #{userId} AND sku_id = #{id}")
    String deleteCart(String userId, String id);

    @Delete("DELETE FROM cart WHERE user_id = #{userId}")
    void deleteCartByUserId(String userId);
    @Select("SELECT " +
            "COALESCE(SUM(c.count), 0) AS goodsCount, " +
            "COALESCE(SUM(s.old_price * c.count), 0) AS totalPrice, " +
            "0 AS postFee, " +  // 占位符，实际在服务层计算
            "0 AS totalPayPrice " +  // 占位符，实际在服务层计算
            "FROM cart c " +
            "JOIN sku s ON c.sku_id = s.id " +
            "WHERE c.user_id = #{userId} AND c.selected = true")
    OrderSummaryVo getOrderSummary(String userId);

//    @Select("INSERT INTO orders (user_id, id, delivery_time_type, pay_type, pay_channel, buyer_message, address_id) VALUES (#{userId}, #{orderId}, #{deliveryTimeType}, #{payType}, #{payChannel}, #{buyerMessage}, #{addressId})")
//    OrderCreateResponse createOrder(String userId,String orderId, Integer deliveryTimeType, Integer payType, Integer payChannel, String buyerMessage, String addressId);

    // 创建订单主记录
    @Insert("INSERT INTO orders (id, user_id, total_num, total_money, post_fee, pay_money, status, " +
            "pay_type, pay_channel, delivery_time_type, buyer_message, receiver_name, receiver_phone, " +
            "receiver_province, receiver_city, receiver_county, receiver_address, pay_latest_time, address_id) " +
            "VALUES (#{id}, #{userId}, #{totalNum}, #{totalMoney}, #{postFee}, #{payMoney}, #{status}, " +
            "#{payType}, #{payChannel}, #{deliveryTimeType}, #{buyerMessage}, #{receiverName}, #{receiverPhone}, " +
            "#{receiverProvince}, #{receiverCity}, #{receiverCounty}, #{receiverAddress}, #{payLatestTime}, #{addressId})")
    void createOrder(Orders order);
    // 获取SKU详情
    @Select("SELECT id, specs->>'$.text' AS attrsText, price FROM sku WHERE id = #{skuId}")
    SkuDetail getSkuDetail(String skuId);    // 创建订单项
    @Insert("INSERT INTO order_item (id,user_id, order_id, sku_id, count, price, attrs_text) " +
            "VALUES (#{id}, #{userId}, #{orderId}, #{skuId}, #{count}, #{price}, #{attrsText})")
    void createOrderItem(@Param("id") String id,String userId, @Param("orderId") String orderId,
                         @Param("skuId") String skuId, @Param("count") int count,
                         @Param("price") double price, @Param("attrsText") String attrsText);
//    @Select("INSERT INTO order_item (id, user_id, order_id, sku_id, count) VALUES (#{id}, #{userId}, #{orderId}, #{skuId}, #{count})")
//    String createOrderItem(String id,String userId,String orderId, String skuId, Integer count);

    @Select("SELECT o.id, " +
            "DATE_FORMAT(o.create_time, '%Y-%m-%d %H:%i:%s') AS createTime, " +
            "DATE_FORMAT(o.pay_latest_time, '%Y-%m-%d %H:%i:%s') AS payLastTime, " +
            "DATE_FORMAT(o.cancel_time, '%Y-%m-%d %H:%i:%s') AS closeTime, " +
            "DATE_FORMAT(o.ship_time, '%Y-%m-%d %H:%i:%s') AS consignTime, " +
            "'' AS arrivalExpectedTime, " +
            "'' AS cityCode, " +
            "TIMESTAMPDIFF(SECOND, NOW(), o.pay_latest_time) AS countdown, " +
            "'' AS countyCode, " +
            "o.delivery_time_type AS deliveryTimeType, " +
            "DATE_FORMAT(o.complete_time, '%Y-%m-%d %H:%i:%s') AS endTime, " +
            "'' AS evaluationTime, " +
            "o.status AS orderStatus, " +
            "o.pay_channel AS payChannel, " +
            "o.pay_money AS payMoney, " +
            "o.pay_type AS payType, " +
            "CASE WHEN o.status = 1 THEN 1 ELSE 0 END AS payStatus, " +
            "DATE_FORMAT(o.pay_time, '%Y-%m-%d %H:%i:%s') AS payTime, " +
            "o.post_fee AS postFee, " +
            "'' AS provinceCode, " +
            "o.receiver_address AS receiverAddress, " +
            "o.receiver_name AS receiverContact, " +
            "o.receiver_phone AS receiverMobile, " +
            "o.total_money AS totalMoney, " +
            "o.total_num AS totalNum " +
            "FROM orders o " +
            "WHERE o.id = #{id} AND o.user_id = #{userId}")
            OrderDetailResponse getOrderDetail(@Param("userId") String userId, @Param("id") String id);

            @Select("SELECT oi.id, g.name, " +
                    "COALESCE(s.picture, g.picture) AS picture, " +
                    "oi.price AS nowPrice, " +
                    "oi.count, " +
                    "s.stock, " +
                    "oi.attrs_text AS attrsText, " +
                    "1 AS selected " +
                    "FROM order_item oi " +
                    "JOIN sku s ON oi.sku_id = s.id " +
                    "JOIN goods g ON s.goods_id = g.id " +
                    "WHERE oi.order_id = #{orderId}")
            List<CartItemDTO> selectOrderItems(String orderId);
    @Select("SELECT * FROM user_address WHERE id = #{id} AND user_id = #{userId}")
    UserAddress getAddressById(String id, String userId);
    @Select("SELECT price FROM sku WHERE id = #{id}")
    double getSkuCurrentPrice(String skuId);

    //       SELECT
    //            id,
    //            DATE_FORMAT(create_time, '%Y-%m-%d %H:%i:%s') AS createTime,
    //            CASE pay_type
    //                WHEN 1 THEN '微信支付'
    //                WHEN 2 THEN '支付宝'
    //                ELSE '其他'
    //            END AS payType,
    //            status AS orderStatus,
    //            TIMESTAMPDIFF(SECOND, NOW(), pay_latest_time) AS countdown,
    //            pay_money / 100.0 AS payMoney,
    //            post_fee / 100.0 AS postFee
    //        FROM orders
    //        WHERE user_id = #{userId}
    //        <if test="orderState != null">
    //            AND status = #{orderState}
    //        </if>
    //        ORDER BY create_time DESC
    //        LIMIT #{offset}, #{pageSize}

    List<OrderListItemDTO> queryOrdersByUser(
            @Param("userId") String userId,
            @Param("orderState") Integer orderState,
            @Param("offset") int offset,
            @Param("pageSize") int pageSize
    );

    int countOrdersByUser(
            @Param("userId") String userId,
            @Param("orderState") Integer orderState
    );
    @Select({
            "SELECT oi.sku_id AS id,",
            "g.name AS name,",
            "s.picture AS image,",
            "oi.attrs_text AS attrsText,",
            "(oi.price / 100.0) AS realPay,",
            "oi.count AS quantity",
            "FROM order_item oi",
            "JOIN sku s ON oi.sku_id = s.id",
            "JOIN goods g ON s.goods_id = g.id",
            "WHERE oi.user_id = #{userId}",
            "ORDER BY oi.created_at DESC"
    })
   OrderSkus batchQueryOrderSkus(@Param("userId") String userId);

    @Select("SELECT sku_id as id, image, name, `attrsText`, price as realPay, count as quantity FROM order_item WHERE user_id = #{userId}")
    List<SkusVo> getCartByUserId(String userId);

    int countOrdersByUserIdAndState(String userId, Integer orderState);

    List<Order> selectOrdersByUserIdAndState(String userId, Integer orderState, int offset, Integer pageSize);

    List<OrderItem> selectOrderItemsByOrderIds(List<String> orderIds);
}
